package com.fingard.dsp.bank.directbank.chinapay03;

import com.fingard.dsp.bank.reqrespobj.transaction.RetAtsTrans;
import com.fingard.dsp.bank.reqrespobj.transaction.detail.RetAtsTransDetail;
import com.fingard.io.FileHelper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.fingard.FGBiz;
import com.fingard.dsp.bank.directbank.DirectBase;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ChinaPay03Base extends DirectBase {

    public static String statementFilePath = FGBiz.dspCenter.dspSet.dataDirPath + File.separator + "tempfile" + File.separator + "ChinaPay03" + File.separator;

    public String readExcel(String filepath) throws Exception {
        try {
            Workbook wb = new HSSFWorkbook(new FileInputStream(filepath));
            Sheet sheet = wb.getSheetAt(0);
            //String dealFlag = getCellValue(sheet.getRow(1).getCell(9));

            StringBuilder sbFileContent = new StringBuilder();
            for (int i = 3; i < sheet.getLastRowNum(); i++) {
                Row detailRow = sheet.getRow(i);
                if (detailRow == null) {// 略过空行
                    continue;
                }
                int cellSize = detailRow.getLastCellNum();
                StringBuilder eachline = new StringBuilder();
                /*
                if (!dealFlag.equals(getCellValue(detailRow.getCell(0)))) {
                    continue;
                }*/
                for (int k = 0; k < cellSize; k++) {
                    Cell cell = detailRow.getCell(k);
                    if (eachline.length() > 0) {
                        eachline.append(",");
                    }
                    eachline.append(getCellValue(cell));
                }
                sbFileContent.append(eachline).append("\r\n");
            }
            return sbFileContent.toString();
        } catch (FileNotFoundException e) {
            return "";
        }
    }

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }

        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                Date theDate = cell.getDateCellValue();
                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cellValue = dff.format(theDate);
            } else {
                cell.setCellType(Cell.CELL_TYPE_STRING);
            }
        }

        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = dff.format(theDate);
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public String readExcelKN(String filepath) throws Exception {
        FileInputStream fileInputStream=null;
        StringBuilder sbFileContent = new StringBuilder();
        try {
            fileInputStream=new FileInputStream(filepath);
            if(fileInputStream.read()>-1){
                Workbook wb = new HSSFWorkbook(new FileInputStream(filepath));
                Sheet sheet = wb.getSheetAt(0);
                StringBuilder twoDetail = new StringBuilder();
                //获取第二行的数据
                Row detailTwo = sheet.getRow(1);
                if (detailTwo != null) {// 略过空行
                    int cellSize = detailTwo.getLastCellNum();
                    for (int k = 0; k < cellSize; k++) {
                        Cell cell = detailTwo.getCell(k);
                        twoDetail.append(getCellValue(cell));
                    }
                }
                //获取表格中的数据
                for (int i = 3; i < sheet.getLastRowNum(); i++) {
                    Row detailRow = sheet.getRow(i);
                    if (detailRow == null) {// 略过空行
                        continue;
                    }
                    int cellSize = detailRow.getLastCellNum();
                    StringBuilder eachline = new StringBuilder();
                    for (int k = 0; k < cellSize; k++) {
                        Cell cell = detailRow.getCell(k);
                        if (eachline.length() > 0) {
                            eachline.append(",");
                        }
                        eachline.append(getCellValue(cell));
                    }
                    sbFileContent.append(eachline).append(","+twoDetail).append("\r\n");
                }
            }
            return sbFileContent.toString();
        } catch (FileNotFoundException e) {
            return "";
        }finally {
            if(fileInputStream!=null){
                fileInputStream.close();
            }
        }
    }

    /**
     * 68对账单解析方式--康宁医院版本
     * //0.清算日期、1.支付方式、2.终端号、3.分店简称、4.交易类型、5.卡号、6.交易金额、7.商户优惠金额、8.钱包优惠金额、9.手续费、10.清算金额
     * //11.交易日期、12.交易时间、13.发卡行、14.参考号、15.担保号、16.卡类型、17.银商订单号、18.备注
     * @param fileName 本地对账单路径
     * @param retAts 响应ats报文
     */
    public void analysis_68_kn(String fileName,String actNum,RetAtsTrans retAts) throws Exception{
        if(FileHelper.existFile(fileName)){
            WriteBankLogLn("开始读取文件：" + fileName);
            String fileContent = readExcelKN(fileName);
            WriteBankLogLn2("对账单重组后响应内容为:\r\n" + fileContent);
            String[] totalLine = fileContent.split("\r\n");
            for (String eachline : totalLine) {
                //0.清算日期、1.支付方式、2.终端号、3.分店简称、4.交易类型、5.卡号、6.交易金额、7.商户优惠金额、8.钱包优惠金额、9.手续费、10.清算金额
                //11.交易日期、12.交易时间、13.发卡行、14.参考号、15.担保号、16.卡类型、17.银商订单号、18.备注 19.表头商户信息
                String[] eachNode = eachline.split(",");
                if (eachNode.length > 19 && eachNode[11].length() > 10) {
                    if (eachNode[19].contains(actNum)) {
                        RetAtsTransDetail tmpRetDetail = new RetAtsTransDetail();
                        tmpRetDetail.actNum = actNum;//银行账号
                        tmpRetDetail.tranDate = eachNode[11].substring(0, 10);//交易日期
                        tmpRetDetail.valueDate = tmpRetDetail.tranDate;//起息日期
                        tmpRetDetail.tranTime = eachNode[11].substring(0, 10) + " " + eachNode[12];//交易时间
                        tmpRetDetail.recAccNum = eachNode[5];//卡号
                        tmpRetDetail.recAccBank = eachNode[13];//发卡行
                        if (eachNode[6].contains("-")) {//借贷标识
                            tmpRetDetail.cdFlag = "1";
                        } else {
                            tmpRetDetail.cdFlag = "2";
                        }
                        tmpRetDetail.tranAmt = eachNode[6].replace("-", "");//交易金额
                        tmpRetDetail.askNumber = eachNode[17];//银商订单号
                        tmpRetDetail.payCode = eachNode[3];//分店简称
                        tmpRetDetail.originator = eachNode[14];//参考号
                        tmpRetDetail.busType = eachNode[1];//支付方式
                        tmpRetDetail.fee = eachNode[9];//手续费
                        tmpRetDetail.urid = eachNode[17];//银商订单号
                        tmpRetDetail.possCode=eachNode[2];//终端号
                        retAts.writeRetDetail(tmpRetDetail);
                    }
                }
            }
        }
    }

    /**
     * 1对账单解析方式--康宁医院版本
     * @param fileName 本地对账单路径
     * @param retAts 响应ats报文
     */
    public void analysis_1_kn(String fileName,String actNum,RetAtsTrans retAts) throws Exception{
        if(FileHelper.existFile(fileName)){
            WriteBankLogLn("开始读取文件：" + fileName);
            String fileContent = readExcelKN(fileName);
            WriteBankLogLn2("对账单重组后响应内容为:\r\n" + fileContent);
            String[] totalLine = fileContent.split("\r\n");
            for (String eachline : totalLine) {
                //0.清算日期 1.终端号 2.分店简称 3.交易类型 4.卡号 5.交易金额 6.手续费 7.清算金额 8.交易日期 9.交易时间
                //10.发卡行 11.参考号 12.授权码 13.卡类型 14.备注 15.表头商户信息
                String[] eachNode = eachline.split(",");
                if (eachNode.length > 15 && eachNode[8].length() > 10) {
                    if (eachNode[15].contains(actNum)) {
                        RetAtsTransDetail tmpRetDetail = new RetAtsTransDetail();
                        tmpRetDetail.actNum = actNum;//银行账号
                        tmpRetDetail.tranDate = eachNode[8].substring(0, 10);//交易日期
                        tmpRetDetail.valueDate = tmpRetDetail.tranDate;//起息日期
                        tmpRetDetail.tranTime = tmpRetDetail.tranDate + " " + eachNode[9];//交易时间
                        tmpRetDetail.recAccNum = eachNode[4];//卡号
                        tmpRetDetail.recAccBank = eachNode[10];//发卡行
                        if (eachNode[5].contains("-")) {//借贷标识
                            tmpRetDetail.cdFlag = "1";
                        } else {
                            tmpRetDetail.cdFlag = "2";
                        }
                        tmpRetDetail.tranAmt = eachNode[5].replace("-", "");//交易金额
                        tmpRetDetail.askNumber = "";//银商订单号
                        tmpRetDetail.payCode = eachNode[2];//分店简称
                        tmpRetDetail.originator = eachNode[11];//参考号
                        tmpRetDetail.busType = "";//支付方式
                        tmpRetDetail.fee = eachNode[6];//手续费
                        tmpRetDetail.urid = "";//唯一明细号
                        tmpRetDetail.possCode = eachNode[1];//终端号
                        retAts.writeRetDetail(tmpRetDetail);
                    }
                }
            }
        }
    }


    /**
     * 86对账单解析方式--康宁医院版本
     * @param fileName 本地对账单路径
     * @param retAts 响应ats报文
     */
    public void analysis_86_kn(String fileName,String actNum,RetAtsTrans retAts) throws Exception{
        if(FileHelper.existFile(fileName)){
            WriteBankLogLn("开始读取文件：" + fileName);
            String fileContent = readExcelKN(fileName);
            WriteBankLogLn2("对账单重组后响应内容为:\r\n" + fileContent);
            String[] totalLine = fileContent.split("\r\n");
            for (String eachline : totalLine) {
                //0.清算日期 1.交易日期 2.交易时间 3.受理商户号 4.内容提供方商户号 5.内容提供方终端号 6.交易金额 7.手续费 8.结算金额
                //9.流水号 10.交易类型 11.便利支付类型 12.支付方式 13.卡号 14.卡类型 15.发卡行 16.预授权号 17.检索参考号 18.受理商户手续费
                //19.订单号/账户/产品代码/手机号 20.面值/充值金额/充值数量 21.彩种 22彩票期次 23.彩票订单号/车牌号 24.支付流水号
                //25.内容提供方平台流水号 26.信用卡号/转入卡号 27.交易返回码 28.表头商户信息
                String[] eachNode = eachline.split(",");
                if (eachNode.length > 28 && eachNode[1].length() > 10) {
                    if (eachNode[28].contains(actNum)) {
                        RetAtsTransDetail tmpRetDetail = new RetAtsTransDetail();
                        tmpRetDetail.actNum = actNum;//银行账号
                        tmpRetDetail.tranDate = eachNode[1].substring(0, 10);//交易日期
                        tmpRetDetail.valueDate = tmpRetDetail.tranDate;//起息日期
                        tmpRetDetail.tranTime = tmpRetDetail.tranDate + " " + eachNode[2];//交易时间
                        tmpRetDetail.recAccNum = eachNode[13];//卡号
                        tmpRetDetail.recAccBank = eachNode[15];//发卡行
                        if (eachNode[6].contains("-")) {//借贷标识
                            tmpRetDetail.cdFlag = "1";
                        } else {
                            tmpRetDetail.cdFlag = "2";
                        }
                        tmpRetDetail.tranAmt = eachNode[6].replace("-", "");//交易金额
                        tmpRetDetail.askNumber = eachNode[24];//银商订单号
                        tmpRetDetail.payCode = "";//分店简称
                        tmpRetDetail.originator = eachNode[17];//参考号
                        tmpRetDetail.busType = eachNode[12];//支付方式
                        tmpRetDetail.fee = eachNode[7];//手续费
                        tmpRetDetail.urid = eachNode[24];//唯一明细号
                        tmpRetDetail.possCode = eachNode[5];//终端号
                        retAts.writeRetDetail(tmpRetDetail);
                    }
                }
            }
        }
    }

    /**
     * 9999对账单解析方式--康宁医院版本
     * @param fileName 本地对账单路径
     * @param retAts 响应ats报文
     */
    public void analysis_9999_kn(String fileName,String actNum,RetAtsTrans retAts) throws Exception{
        if(FileHelper.existFile(fileName)){
            WriteBankLogLn("开始读取文件：" + fileName);
            String fileContent = readExcelKN(fileName);
            WriteBankLogLn2("对账单重组后响应内容为:\r\n" + fileContent);
            String[] totalLine = fileContent.split("\r\n");
            for (String eachline : totalLine) {
                //0.清算日期 1.交易日期 2.交易时间 3.终端号 4.卡号 5.交易类型 6.交易金额 7.清算金额 8.手续费
                //9.参考号 10.流水号 11.卡类型 12.授权码 13.表头商户信息
                String[] eachNode = eachline.split(",");
                if (eachNode.length > 13 && eachNode[1].length() > 10) {
                    if (eachNode[13].contains(actNum)) {
                        RetAtsTransDetail tmpRetDetail = new RetAtsTransDetail();
                        tmpRetDetail.actNum = actNum;//银行账号
                        tmpRetDetail.tranDate = eachNode[1].substring(0, 10);//交易日期
                        tmpRetDetail.valueDate = tmpRetDetail.tranDate;//起息日期
                        tmpRetDetail.tranTime = tmpRetDetail.tranDate + " " + eachNode[2];//交易时间
                        tmpRetDetail.recAccNum = eachNode[4];//卡号
                        tmpRetDetail.recAccBank = "";//发卡行
                        if (eachNode[6].contains("-")) {//借贷标识
                            tmpRetDetail.cdFlag = "1";
                        } else {
                            tmpRetDetail.cdFlag = "2";
                        }
                        tmpRetDetail.tranAmt = eachNode[6].replace("-", "");//交易金额
                        tmpRetDetail.askNumber = eachNode[10];//银商订单号
                        tmpRetDetail.payCode = "";//分店简称
                        tmpRetDetail.originator = eachNode[9];//参考号
                        tmpRetDetail.busType = "";//支付方式
                        tmpRetDetail.fee = eachNode[8];//手续费
                        tmpRetDetail.urid = eachNode[10];//唯一明细号
                        tmpRetDetail.possCode = eachNode[3];//终端号
                        retAts.writeRetDetail(tmpRetDetail);
                    }
                }
            }
        }
    }

}
